﻿Imports System.Data.SqlServerCe

Public Class SharedItemsFiles
    Shared Sub New()
        Dim aSelectColumns As String() = {"Id", "FileName", "Directory", "SHA512Hash" _
                                        , "Size", "SharedItemId", "Added", "LastChanged" _
                                        , "OrphanedSince"}
        ObjectPersister.DefineObject(GetType(Entities.SharedItemFile), "SharedItemsFiles", aSelectColumns)
    End Sub
    Private Sub New()
    End Sub

    Public Shared Function GetAllByDirectory(ByVal directory As String) As Generic.List(Of Entities.SharedItemFile)
        Return ObjectPersister.GetAll(Of Entities.SharedItemFile)(String.Format("Directory='{0}' AND OrphanedSince IS NULL", directory))
    End Function

    Public Shared Function GetAllBySharedItemId(ByVal sharedItemId As Integer) As Generic.List(Of Entities.SharedItemFile)
        Return ObjectPersister.GetAll(Of Entities.SharedItemFile)(String.Format("SharedItemId='{0}'", sharedItemId))
    End Function

    Public Shared Function GetAllByFileNameAndSize(ByVal filename As String, ByVal size As Long) As Generic.List(Of Entities.SharedItemFile)
        Dim strClause As String = String.Format("[FileName]=@Filename AND [Size]=@Size", filename, size)
        Dim sqlPar1 As New SqlCeParameter("@Filename", filename)
        Dim sqlPar2 As New SqlCeParameter("@Size", size)
        Return ObjectPersister.GetAll(Of Entities.SharedItemFile)(strClause, sqlPar1, sqlPar2)
    End Function
    Public Shared Function GetAllBySHA512Hash(ByVal sha512Hash As String) As Generic.List(Of Entities.SharedItemFile)
        Dim strClause As String = String.Format("[Sha512Hash]=@Hash", sha512Hash)
        Dim sqlPar1 As New SqlCeParameter("@Hash", sha512Hash)
        Return ObjectPersister.GetAll(Of Entities.SharedItemFile)(strClause, sqlPar1)
    End Function

    Public Shared Function GetAllBySourceWatchFolderId(ByVal watchFolderId As Integer) As Generic.List(Of Entities.SharedItemFile)
        Dim strClause As String = String.Format("SharedItemId IN (SELECT Id FROM SharedItems WHERE SourceWatchFolderId = {0})", watchFolderId)
        Return ObjectPersister.GetAll(Of Entities.SharedItemFile)(strClause)
    End Function

    Public Shared Sub SetFileAsOrphan(ByVal item As Entities.SharedItemFile, ByVal transactionScope As TransactionScope)
        'Dim strSql As String = String.Format("DELETE FROM SharedItemsFiles WHERE Id={0}", item.Id)
        Dim strSql As String
        strSql = String.Format("UPDATE SharedItemsFiles SET SharedItemId = 0, OrphanedSince=getdate(), Directory='' WHERE Id={0}", item.Id)
        Environment.ExecuteNonQuery(strSql, transactionScope)
    End Sub
    Public Shared Sub DeleteBySharedItemId(ByVal sharedItemId As Integer, ByVal transactionScope As TransactionScope)
        Dim strSql As String = String.Format("DELETE FROM SharedItemsFiles WHERE SharedItemId={0}", sharedItemId)
        Environment.ExecuteNonQuery(strSql, transactionScope)
    End Sub

    Public Shared Sub ReevalOrphanedSince(ByVal transactionScope As TransactionScope)
        Dim strSql As String = "UPDATE SharedItemsFiles SET OrphanedSince=getdate() " & _
                               "WHERE SharedItemId NOT IN (SELECT Id FROM SharedItems) " & _
                               "  AND OrphanedSince IS NULL"
        Environment.ExecuteNonQuery(strSql, transactionScope)

        strSql = "UPDATE SharedItemsFiles SET OrphanedSince=NULL " & _
                 "WHERE SharedItemId IN (SELECT Id FROM SharedItems) " & _
                 "  AND OrphanedSince IS NOT NULL"
        Environment.ExecuteNonQuery(strSql, transactionScope)
    End Sub

    Public Shared Sub RemoveOldOrphans(ByVal orphanedSince As Date, ByVal transactionScope As TransactionScope)
        Dim strSql As String = "DELETE FROM SharedItemsFiles WHERE OrphanedSince<@OrphanedSince AND OrphanedSince IS NOT NULL"
        Dim sqlCmd As SqlCeCommand = Environment.NewCommand(strSql, transactionScope)
        sqlCmd.Parameters.AddWithValue("@OrphanedSince", orphanedSince)

        Environment.ExecuteNonQuery(sqlCmd)
    End Sub

    Public Shared Sub Save(ByVal item As Entities.SharedItemFile)
        Using t As New TransactionScope
            Save(item, t)
            t.Complete()
        End Using
    End Sub

    Public Shared Sub Save(ByVal item As Entities.SharedItemFile, ByVal transactionScope As TransactionScope)
        Dim clsCmd As New SaveCommand(Of Integer)("SharedItemsFiles", "Id", item.Id, 0)
        clsCmd.AddParam("FileName", item.FileName, True)
        clsCmd.AddParam("Directory", item.Directory, True)
        clsCmd.AddParam("SHA512Hash", item.SHA512Hash, True)
        clsCmd.AddParam("Size", item.Size, True)
        clsCmd.AddParam("SharedItemId", item.SharedItemId, True)
        clsCmd.AddParam("Added", item.Added, True)
        clsCmd.AddParam("LastChanged", item.LastChanged, True)

        item.Id = clsCmd.Save(transactionScope)
    End Sub

End Class
